/**
 * @function: 数据库操作类
 * @modify history
 * Date           Author            Description
 * --------------------------------------------------------
 * 2010/01/12     fan.kebin         Created
 *  
 */
package com.byxm.wlgw.common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.log4j.Category;

public class DataBaseAccess {

	/** 日志 */
	private static Category cat = Category.getInstance(new Throwable().getStackTrace()[0].getClassName());

	//private static String jndiName = "jdbc/InvestSys";
	/** 数据库连接对象 */
	private Connection conn;
	/**  */
	private Statement stmt;
	/**  */
	private PreparedStatement pstmt;
	/** 锁表标志 */
	private boolean lockFlg = false;

//	private String lastPrepareSQL;
	private static DataSource ds;
	
	static {
		//获得数据库源
		InitialContext initCtx = null;
		try {
			initCtx = new InitialContext();
			ds = (DataSource) initCtx.lookup("java:comp/env/" + ComConst.JNDI_NAME);
		} catch (Exception ex) {
			System.out.println(" DataBaseAccess Init Connection Error : " + ex.toString());
			cat.info(" DataBaseAccess Init Connection Error : " + ex.toString());
		} finally {
			if (initCtx != null) {
				try {
					initCtx.close();
				} catch (NamingException ex) {
					cat.error("NamingException Msg : " + ex.toString());
				}
			}
		}
	}

	/**
	 * 初始化数据库连接
	 * <P>不自动提交数据库操作</p>
	 * 
	 * @throws Exception
	 */
	public DataBaseAccess() throws Exception {
		try {
			conn = ds.getConnection();
			conn.setAutoCommit(false);
		} catch (Exception ex) {
			System.out.println(" DataBaseAccess Init Connection Error : " + ex.toString());
			cat.info(" DataBaseAccess Init Connection Error : " + ex.toString());
		} 
	}


	/**
	 * SQL语句查询
	 * 
	 * @param sql String SQL语句
	 * @throws SQLException
	 * @return ResultSet 数据集
	 */
	public ResultSet executeQuery(String sql) throws SQLException {
		cat.info("executeQuery() sql:" + sql);
		ResultSet rslt = null;
		try {
			if (stmt != null) {
				stmt.close();
			}
			stmt = conn.createStatement();
			rslt = stmt.executeQuery(sql);
		} catch (SQLException e) {
			cat.error("executeQuery():" + e.getMessage());
			throw e;
		}
		return rslt;
	}

	/**
	 * SQL语句更新
	 * 
	 * @param sql
	 *            String SQL语句
	 * @throws SQLException
	 * @return int 更新记录数
	 */
	public int executeUpdate(String sql) throws SQLException {
		//cat.info("executeUpdate()  Sql:" + sql);
		int status = 0;
		try {
			if (stmt != null) {
				stmt.close();
			}
			stmt = conn.createStatement();
			status = stmt.executeUpdate(sql);

		} catch (SQLException e) {
			cat.error("executeUpdate():" + e.getMessage());
			throw e;
		}
		return status;
	}

	/**
	 * 锁定表
	 * 
	 * @param tables String[] 要锁定的表
	 * @throws Exception
	 */
	public void lockTables(String[] tables) throws Exception {
		try {
			if (tables != null && tables.length > 0) {
				StringBuffer sql = new StringBuffer();
				sql.delete(0, sql.length());
				sql.append("LOCK TABLES");
				for (int i = 0; i < tables.length; i++) {
					if (i > 0) {
						sql.append(" , " + tables[i] + " WRITE");
					} else {
						sql.append(" " + tables[i] + " WRITE");
					}
				}
				executeUpdate(sql.toString());
				lockFlg = true;
			}
		} catch (Exception e) {
			cat.error("lockTables() : " + e.getMessage());
			throw e;
		}
	}

	/**
	 * 锁定表
	 * 
	 * @param tables HashMap
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public void lockTables(HashMap tables) throws Exception {
		try {
			if (tables != null && !tables.isEmpty()) {
				StringBuffer sql = new StringBuffer();
				sql.delete(0, sql.length());
				sql.append("LOCK TABLES");

				Iterator itx = tables.entrySet().iterator();
				boolean firstFlg = true;
				while (itx.hasNext()) {
					Map.Entry entry = (Map.Entry) itx.next();
					if (firstFlg) {
						sql.append(" " + entry.getKey() + " "
								+ entry.getValue());
						firstFlg = false;
					} else {
						sql.append(" , " + entry.getKey() + " "
								+ entry.getValue());
					}
				}
				executeUpdate(sql.toString());
				lockFlg = true;
			}
		} catch (Exception e) {
			cat.error("lockTables() : " + e.getMessage());
			throw e;
		}
	}

	/**
	 * 数据库事务提交
	 * 
	 * @throws Exception
	 */
	public void commit() throws Exception {
		try {
			if (conn != null && !conn.isClosed()) {
				conn.commit();
				if (lockFlg) {
					executeUpdate("UNLOCK TABLES");
					lockFlg = false;
				}
				//cat.info("数据库事务提交");
			}
		} catch (Exception e) {
			cat.error("commit() : " + e.getMessage());
			throw e;
		}
	}

	/**
	 * 数据库事务回滚
	 * 
	 * @throws Exception
	 */
	public void rollback() throws Exception {
		try {
			if (conn != null && !conn.isClosed()) {
				conn.rollback();
				if (lockFlg) {
					executeUpdate("UNLOCK TABLES");
					lockFlg = false;
				}
				//cat.info("数据库事务回滚");
			}
		} catch (Exception e) {
			cat.error("rollback() : " + e.getMessage());
			throw e;
		}
	}

	/**
	 * 释放数据库连接至连接池
	 * 
	 * @throws Exception
	 */
	public void releaseConnect() throws Exception {
		try {
			if (lockFlg) {
				executeUpdate("UNLOCK TABLES");
				lockFlg = false;
			}
			if (stmt != null) {
				stmt.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			if (conn != null && !conn.isClosed()) {
				conn.close();
				conn = null;
			}
			//cat.info("数据库连接关闭");
		} catch (Exception e) {
			cat.error("releaseConnect() : " + e.getMessage());
			throw e;
		}
	}

	/**
	 * 清除对象时关闭数据库连接
	 */
	public void finalize() {
		try {
			if (conn != null && !conn.isClosed()) {
				conn.close();
			}
		} catch (Exception e) {
			cat.error("finalize() : " + e.getMessage());
		}
	}

	public Connection getConn() {
		return conn;
	}

	public PreparedStatement getPstmt(String sql) throws Exception {
		if(conn != null) {
			return conn.prepareStatement(sql);
		}
		return null;
	}
	

	public PreparedStatement getPstmtWithKey(String sql,String[] id) throws Exception {
		if(conn != null) {
			return conn.prepareStatement(sql,id);
		}
		return null;
	}
	
	public void setConn(Connection conn) {
		this.conn = conn;
	}
}